package tsli.acm.dao;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import tsli.acm.database.Database;

public class SFCCommissionPositiveDAO {
	private Database db;

	public SFCCommissionPositiveDAO(Database db) {
		this.db = db;
	}

	public List<Map<String, Object>> getAllAgentStructure() {
		String vaSql = "SELECT * " + "FROM TSLI_SFC_COMMISSION_POSITIVE";
		List<Map<String, Object>> result = db.queryList(vaSql);

		if (result != null && result.size() > 0) {
			return result;
		} else {
			return null;
		}
	}

	public String[][] findFYPForCalRA(String paEmployeeId, String paDate) {
//		String vaSql = "SELECT clos_ym, pe_no_col, po_no, SUM(fyp) AS sum_fyp FROM tsli_sfc_commission_positive " 
//				+ "WHERE pe_no_col = ? AND " 
//				+ "MONTHS_BETWEEN (TO_DATE(?, 'YYYYMM'), TO_DATE(CLOS_YM, 'YYYYMM') ) between 0 and 5 " 
//				+ "GROUP BY clos_ym, pe_no_col, po_no";
		String vaSql = "SELECT b.clos_ym, b.po_no, b.pe_no_col, sum(b.sum_fyp) AS sum_fyp, a.fst_enco_mms FROM ( "
				+ "SELECT pe_no_rcrut, pe_no, nm_title_thai, nm_thai, surnm_thai, fst_enco_mms "
				+ "FROM tsli_sfc_agent_structure "
				+ "WHERE DTY_CD IN ('S10240205','S10240206') AND (fst_enco_mms BETWEEN 1 AND 6) "
				+ "AND clos_ym = ?) a "
				+ "JOIN ( "
				+ "SELECT clos_ym, pe_no_col, po_no, SUM(fyp) AS sum_fyp FROM tsli_sfc_commission_positive "
				+ "WHERE MONTHS_BETWEEN (TO_DATE(?, 'YYYYMM'), TO_DATE(CLOS_YM, 'YYYYMM')) between 0 and 5 " 
				+ "GROUP BY clos_ym, pe_no_col, po_no) b "
				+ "ON a.pe_no = b.pe_no_col " 
				+ "WHERE b.pe_no_col = ? "
				+ "GROUP BY a.fst_enco_mms, b.clos_ym, b.po_no, b.pe_no_col " 
				+ "ORDER BY b.clos_ym";
		List<Map<String, Object>> voResult = db.queryList(vaSql, paDate, paDate, paEmployeeId);
    	String[][] vaResult = null;
    	if (voResult != null) {
    		vaResult = new String[voResult.size()][5];
    		for (int i = 0; i < voResult.size(); i++) {
    			Map<String, Object> voTmp = voResult.get(i);
    			vaResult[i][0] = (String) voTmp.get("CLOS_YM");
    			vaResult[i][1] = ((BigDecimal) voTmp.get("SUM_FYP")).toString();
    			vaResult[i][2] = (String) voTmp.get("PO_NO");
    			vaResult[i][3] = (String) voTmp.get("PE_NO_COL");
    			vaResult[i][4] = (String) voTmp.get("FST_ENCO_MMS");
    		}
            return vaResult;
        } else {
            return null;
        }
	}
	
	public List<Map<String, Object>> findcalculateFA(String paPCPotive,String paPCNegative,String paClosYm,String paPeNo) {
		String vaSql = "SELECT clos_ym,"
				+"PE_NO,"
				+"fst_enco_mms,"
				+ "bizrt_itm_data,"
				+ "bizrt_itm_amt,"
				+"PC,"
				+"FA,"
				+"CASE WHEN PERSISTENCY_RATE IS NULL THEN 'N/A' ELSE CAST(PERSISTENCY_RATE AS VARCHAR(3)) END AS PERSISTENCY_RATE,"
				+"CASE"
				+ "				 WHEN fst_enco_mms = '1' THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '2' THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '3' AND PERSISTENCY_RATE >= 98 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '4' AND PERSISTENCY_RATE >= 97 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '5' AND PERSISTENCY_RATE >= 96 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '6' AND PERSISTENCY_RATE >= 95 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '7' AND PERSISTENCY_RATE >= 94 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '8' AND PERSISTENCY_RATE >= 93 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '9' AND PERSISTENCY_RATE >= 92 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '10' AND PERSISTENCY_RATE >= 91 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '11' AND PERSISTENCY_RATE >= 90 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '12' AND PERSISTENCY_RATE >= 89 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 ELSE "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN (FA*0.90) ELSE 0 END"
				+ "				 END FA_TOTAL"
				+" FROM(SELECT commission_positive.clos_ym,"
				+" agent_structure.PE_NO,"
				+" tsli_standard_activity.bizrt_itm_data,tsli_standard_activity.bizrt_itm_amt,"
				+" agent_structure.fst_enco_mms,"
				+" "+(Double.parseDouble(paPCPotive)-Double.parseDouble(paPCNegative))+" AS PC,"
				+" CASE "
				+" WHEN "+(Double.parseDouble(paPCPotive)-Double.parseDouble(paPCNegative))+" < 10000 THEN 0"
				+" WHEN "+(Double.parseDouble(paPCPotive)-Double.parseDouble(paPCNegative))+" < 20000 THEN 2500"
				+" WHEN "+(Double.parseDouble(paPCPotive)-Double.parseDouble(paPCNegative))+" < 30000 THEN 5000"
				+" WHEN "+(Double.parseDouble(paPCPotive)-Double.parseDouble(paPCNegative))+" < 40000 THEN 7500"
				+" WHEN "+(Double.parseDouble(paPCPotive)-Double.parseDouble(paPCNegative))+" < 50000 THEN 10500"
				+" WHEN "+(Double.parseDouble(paPCPotive)-Double.parseDouble(paPCNegative))+" < 70000 THEN 13500"
				+" ELSE 19000 END AS FA ,"
				+" CASE WHEN SUM(persistency.PRST_BIZRT) IS NULL OR SUM(persistency.SOLI_BIZRT) IS NULL THEN NULL ELSE NVL(CAST(((SUM(persistency.PRST_BIZRT) / SUM(persistency.SOLI_BIZRT)) * 100) AS Decimal(20,2)),0) END AS PERSISTENCY_RATE"
				+" FROM tsli_sfc_commission_positive  commission_positive"
				+ "              LEFT JOIN tsli_sfc_agent_structure  agent_structure"
				+ "               ON commission_positive.PE_NO_COL = agent_structure.PE_NO "
				+ "               LEFT JOIN tsli_standard_activity  tsli_standard_activity"
				+ "               ON tsli_standard_activity.PE_NO = commission_positive.PE_NO_COL  "
				+ "               LEFT JOIN tsli_sfc_commission_negative  commission_negative "
				+ "               ON commission_negative.PE_NO_COL = commission_positive.PE_NO_COL AND commission_negative.clos_ym = ?"
				+ "               LEFT JOIN TSLI_PERSISTENCY persistency "
				+ "               ON persistency.PE_NO = commission_positive.PE_NO_COL  "
				+" WHERE  ( agent_structure.fst_enco_mms BETWEEN 1 AND 12 ) "
				+" AND commission_positive.clos_ym = ?"
				+" AND agent_structure.dty_cd  in ('S10240205','S10240206') "
				+" GROUP BY commission_positive.clos_ym , agent_structure.PE_NO ,agent_structure.fst_enco_mms , tsli_standard_activity.bizrt_itm_data, tsli_standard_activity.bizrt_itm_amt)"
				+ "WHERE PE_NO = ?";

		List<Map<String, Object>> result = db.queryList(vaSql, paClosYm,paClosYm ,paPeNo);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	public List<Map<String, Object>> findcalculateFAReCAl(String paClosYm,String paPeNo) {
		
		
		int vnThisMonth = Integer.parseInt(paClosYm);
		int vnBeforeOneMonth = Integer.parseInt(paClosYm)-1;
		int vnBeforeTwoMonth = Integer.parseInt(paClosYm)-2;

		String vnPCPositiveThisMonth = this.getPCPositive(paPeNo, vnThisMonth+"");
		String vnPCNegativeThisMonth = this.getPCNegative(paPeNo, vnThisMonth+"");
		
		String vnPCPositiveBeforeOneMonth = this.getPCPositive(paPeNo, vnBeforeOneMonth+"");
		String vnPCNegativeBeforeOneMonth = this.getPCNegative(paPeNo, vnBeforeOneMonth+"");
		
		String vnPCPositiveBeforeTwoMonth = this.getPCPositive(paPeNo, vnBeforeTwoMonth+"");
		String vnPCNegativeBeforeTwoMonth = this.getPCNegative(paPeNo, vnBeforeTwoMonth+"");
		
		String vaSql = "SELECT clos_ym,"
				+ "				 		PE_NO,"
				+ "				        fst_enco_mms,"
				+ "				        NVL(PC,0) AS PC,"
				+ "				        NVL(FA,0) AS FA,"
				+ "				        CASE WHEN PERSISTENCY_RATE IS NULL THEN 'N/A' ELSE CAST(PERSISTENCY_RATE AS VARCHAR(3)) END AS PERSISTENCY_RATE,"
				+ "				        NVL(FA_TOTAL,0) AS FA_TOTAL,"
				+ "                		NVL(bizrt_itm_data,'N') AS BIZRT_ITM_DATA,"
				+ "                		NVL(PC_BEFORE_ONE,0) AS PC_BEFORE_ONE,"
				+ "				        NVL(FA_BEFORE_ONE,0) AS FA_BEFORE_ONE,"
				+ "				        CASE WHEN PERSISTENCY_RATE_BEFORE_ONE IS NULL THEN 'N/A' ELSE CAST(PERSISTENCY_RATE_BEFORE_ONE AS VARCHAR(3)) END AS PERSISTENCY_RATE_BEFORE_ONE,"
				+ "				        NVL(FA_TOTAL_BEFORE_ONE,0) AS FA_TOTAL_BEFORE_ONE,"
				+ "                		NVL(BIZT_ITM_DATA_BEFORE_ONE,'N') AS BIZRT_ITM_DATA_BEFORE_ONE,"
				+ "                		NVL(PC_BEFORE_TWO,0) AS PC_BEFORE_TWO,"
				+ "				        NVL(FA_BEFORE_TWO,0) AS FA_BEFORE_TWO,"
				+ "				        CASE WHEN PERSISTENCY_RATE_BEFORE_TWO IS NULL THEN 'N/A' ELSE CAST(PERSISTENCY_RATE_BEFORE_TWO AS VARCHAR(3)) END AS PERSISTENCY_RATE_BEFORE_TWO,"
				+ "				        NVL(FA_TOTAL_BEFORE_TWO,0) AS FA_TOTAL_BEFORE_TWO,"
				+ "                		NVL(BIZT_ITM_DATA_BEFORE_TWO,'N') AS BIZRT_ITM_DATA_BEFORE_TWO,"
				+ "				        CASE "
				+ "				        WHEN FA_RECAL > 0 AND bizrt_itm_data = 'Y' THEN FA_RECAL"
				+ "				          ELSE 0 "
				+ "				        END AS FA_RECAL,"
				+ "						NVL(FA_CAL,0) AS FA_CAL"
				+ "				 FROM (SELECT  THIS_MONTH.clos_ym,"
				+ "				              THIS_MONTH.PE_NO,"
				+ "				              THIS_MONTH.fst_enco_mms,"
				+ "				              THIS_MONTH.PC,"
				+ "				              THIS_MONTH.FA,"
				+ "				              THIS_MONTH.PERSISTENCY_RATE,"
				+ "				              THIS_MONTH.FA_TOTAL,"
				+ "                      THIS_MONTH.bizrt_itm_data,"
				+ "                      BEFORE_ONE_MONTH.PC AS PC_BEFORE_ONE,"
				+ "				              BEFORE_ONE_MONTH.FA AS FA_BEFORE_ONE,"
				+ "				              BEFORE_ONE_MONTH.PERSISTENCY_RATE AS PERSISTENCY_RATE_BEFORE_ONE,"
				+ "				              BEFORE_ONE_MONTH.FA_TOTAL AS FA_TOTAL_BEFORE_ONE,"
				+ "                      BEFORE_ONE_MONTH.bizrt_itm_data AS BIZT_ITM_DATA_BEFORE_ONE,"
				+ "                      BEFORE_TWO_MONTH.PC AS PC_BEFORE_TWO,"
				+ "				              BEFORE_TWO_MONTH.FA AS FA_BEFORE_TWO,"
				+ "				              BEFORE_TWO_MONTH.PERSISTENCY_RATE AS PERSISTENCY_RATE_BEFORE_TWO,"
				+ "				              BEFORE_TWO_MONTH.FA_TOTAL AS FA_TOTAL_BEFORE_TWO,"
				+ "                      BEFORE_TWO_MONTH.bizrt_itm_data AS BIZT_ITM_DATA_BEFORE_TWO,"
				+ "				CASE "
				+ "	              WHEN ((NVL(THIS_MONTH.PC,0) + NVL(BEFORE_ONE_MONTH.PC,0) + NVL(BEFORE_TWO_MONTH.PC,0))/3) < 10000 "
				+ "              AND NVL(THIS_MONTH.PC,0) > 10000 AND THIS_MONTH.fst_enco_mms > 1"
				+ "              AND NVL(BEFORE_ONE_MONTH.PC,0) > 10000 AND BEFORE_ONE_MONTH.fst_enco_mms > 1"
				+ "              AND NVL(BEFORE_TWO_MONTH.PC,0) > 10000 AND BEFORE_TWO_MONTH.fst_enco_mms > 1"
				+ "	                THEN 0"
				+ "	              WHEN ((NVL(THIS_MONTH.PC,0) + NVL(BEFORE_ONE_MONTH.PC,0) + NVL(BEFORE_TWO_MONTH.PC,0))/3) < 20000 "
				+ "              AND NVL(THIS_MONTH.PC,0) > 10000 AND THIS_MONTH.fst_enco_mms > 1"
				+ "              AND NVL(BEFORE_ONE_MONTH.PC,0) > 10000 AND BEFORE_ONE_MONTH.fst_enco_mms > 1"
				+ "              AND NVL(BEFORE_TWO_MONTH.PC,0) > 10000 AND BEFORE_TWO_MONTH.fst_enco_mms > 1"
				+ "	                THEN (2500*3) - (NVL(THIS_MONTH.FA,0) + NVL(BEFORE_ONE_MONTH.FA,0) + NVL(BEFORE_TWO_MONTH.FA,0))"
				+ "	              WHEN ((NVL(THIS_MONTH.PC,0) + NVL(BEFORE_ONE_MONTH.PC,0) + NVL(BEFORE_TWO_MONTH.PC,0))/3) < 30000 "
				+ "              AND NVL(THIS_MONTH.PC,0) > 10000 AND THIS_MONTH.fst_enco_mms > 1"
				+ "              AND NVL(BEFORE_ONE_MONTH.PC,0) > 10000 AND BEFORE_ONE_MONTH.fst_enco_mms > 1"
				+ "              AND NVL(BEFORE_TWO_MONTH.PC,0) > 10000 AND BEFORE_TWO_MONTH.fst_enco_mms > 1"
				+ "	                THEN (5000*3) - (NVL(THIS_MONTH.FA,0) + NVL(BEFORE_ONE_MONTH.FA,0) + NVL(BEFORE_TWO_MONTH.FA,0))"
				+ "	              WHEN ((NVL(THIS_MONTH.PC,0) + NVL(BEFORE_ONE_MONTH.PC,0) + NVL(BEFORE_TWO_MONTH.PC,0))/3) < 40000 "
				+ "              AND NVL(THIS_MONTH.PC,0) > 10000 AND THIS_MONTH.fst_enco_mms > 1"
				+ "              AND NVL(BEFORE_ONE_MONTH.PC,0) > 10000 AND BEFORE_ONE_MONTH.fst_enco_mms > 1"
				+ "              AND NVL(BEFORE_TWO_MONTH.PC,0) > 10000 AND BEFORE_TWO_MONTH.fst_enco_mms > 1"
				+ "	                THEN (7500*3) - (NVL(THIS_MONTH.FA,0) + NVL(BEFORE_ONE_MONTH.FA,0) + NVL(BEFORE_TWO_MONTH.FA,0))"
				+ "	              WHEN ((NVL(THIS_MONTH.PC,0) + NVL(BEFORE_ONE_MONTH.PC,0) + NVL(BEFORE_TWO_MONTH.PC,0))/3) < 50000 "
				+ "              AND NVL(THIS_MONTH.PC,0) > 10000 AND THIS_MONTH.fst_enco_mms > 1"
				+ "              AND NVL(BEFORE_ONE_MONTH.PC,0) > 10000 AND BEFORE_ONE_MONTH.fst_enco_mms > 1"
				+ "              AND NVL(BEFORE_TWO_MONTH.PC,0) > 10000 AND BEFORE_TWO_MONTH.fst_enco_mms > 1"
				+ "	                THEN (10500*3) - (NVL(THIS_MONTH.FA,0) + NVL(BEFORE_ONE_MONTH.FA,0) + NVL(BEFORE_TWO_MONTH.FA,0))"
				+ "	              WHEN ((NVL(THIS_MONTH.PC,0) + NVL(BEFORE_ONE_MONTH.PC,0) + NVL(BEFORE_TWO_MONTH.PC,0))/3) < 70000 "
				+ "              AND NVL(THIS_MONTH.PC,0) > 10000 AND THIS_MONTH.fst_enco_mms > 1"
				+ "              AND NVL(BEFORE_ONE_MONTH.PC,0) > 10000 AND BEFORE_ONE_MONTH.fst_enco_mms > 1"
				+ "              AND NVL(BEFORE_TWO_MONTH.PC,0) > 10000 AND BEFORE_TWO_MONTH.fst_enco_mms > 1"
				+ "	                THEN (13500*3) - (NVL(THIS_MONTH.FA,0) + NVL(BEFORE_ONE_MONTH.FA,0) + NVL(BEFORE_TWO_MONTH.FA,0))"
				+ "	            ELSE (19000*3) - (NVL(THIS_MONTH.FA,0) + NVL(BEFORE_ONE_MONTH.FA,0) + NVL(BEFORE_TWO_MONTH.FA,0)) END AS FA_RECAL,"
				+ "            CASE "
				+ "              WHEN ((NVL(THIS_MONTH.PC,0) + NVL(BEFORE_ONE_MONTH.PC,0) + NVL(BEFORE_TWO_MONTH.PC,0))/3) < 10000 "
				+ "                THEN 0"
				+ "              WHEN ((NVL(THIS_MONTH.PC,0) + NVL(BEFORE_ONE_MONTH.PC,0) + NVL(BEFORE_TWO_MONTH.PC,0))/3) < 20000 "
				+ "                THEN 2500"
				+ "              WHEN ((NVL(THIS_MONTH.PC,0) + NVL(BEFORE_ONE_MONTH.PC,0) + NVL(BEFORE_TWO_MONTH.PC,0))/3) < 30000 "
				+ "                THEN 5000"
				+ "              WHEN ((NVL(THIS_MONTH.PC,0) + NVL(BEFORE_ONE_MONTH.PC,0) + NVL(BEFORE_TWO_MONTH.PC,0))/3) < 40000 "
				+ "                THEN 7500"
				+ "              WHEN ((NVL(THIS_MONTH.PC,0) + NVL(BEFORE_ONE_MONTH.PC,0) + NVL(BEFORE_TWO_MONTH.PC,0))/3) < 50000 "
				+ "                THEN 10500"
				+ "              WHEN ((NVL(THIS_MONTH.PC,0) + NVL(BEFORE_ONE_MONTH.PC,0) + NVL(BEFORE_TWO_MONTH.PC,0))/3) < 70000 "
				+ "                THEN 13500"
				+ "            ELSE 19000 END AS FA_CAL"
				+ "  FROM (SELECT clos_ym,"
				+ "                PE_NO,"
				+ "                fst_enco_mms,"
				+ "                PC,"
				+ "                FA,"
				+ "                CASE WHEN PERSISTENCY_RATE IS NULL THEN 'N/A' ELSE CAST(PERSISTENCY_RATE AS VARCHAR(3)) END AS PERSISTENCY_RATE,"
				+"CASE"
				+ "				 WHEN fst_enco_mms = '1'  THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '2'  THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '3' AND PERSISTENCY_RATE >= 98 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '4' AND PERSISTENCY_RATE >= 97 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '5' AND PERSISTENCY_RATE >= 96 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '6' AND PERSISTENCY_RATE >= 95 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '7' AND PERSISTENCY_RATE >= 94 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '8' AND PERSISTENCY_RATE >= 93 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '9' AND PERSISTENCY_RATE >= 92 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '10' AND PERSISTENCY_RATE >= 91 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '11' AND PERSISTENCY_RATE >= 90 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '12' AND PERSISTENCY_RATE >= 89 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 ELSE "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN (FA*0.90) ELSE 0 END"
				+ "				 END FA_TOTAL,"
				+ "				bizrt_itm_data"
				+ "        FROM(SELECT commission_positive.clos_ym,"
				+ "                      agent_structure.PE_NO,"
				+ "                      agent_structure.fst_enco_mms,"
				+ "                      tsli_standard_activity.bizrt_itm_data,"
				+" "+(Double.parseDouble(vnPCPositiveThisMonth)-Double.parseDouble(vnPCNegativeThisMonth))+" AS PC,"
				+" CASE "
				+" WHEN "+(Double.parseDouble(vnPCPositiveThisMonth)-Double.parseDouble(vnPCNegativeThisMonth))+" < 10000 THEN 0"
				+" WHEN "+(Double.parseDouble(vnPCPositiveThisMonth)-Double.parseDouble(vnPCNegativeThisMonth))+" < 20000 THEN 2500"
				+" WHEN "+(Double.parseDouble(vnPCPositiveThisMonth)-Double.parseDouble(vnPCNegativeThisMonth))+" < 30000 THEN 5000"
				+" WHEN "+(Double.parseDouble(vnPCPositiveThisMonth)-Double.parseDouble(vnPCNegativeThisMonth))+" < 40000 THEN 7500"
				+" WHEN "+(Double.parseDouble(vnPCPositiveThisMonth)-Double.parseDouble(vnPCNegativeThisMonth))+" < 50000 THEN 10500"
				+" WHEN "+(Double.parseDouble(vnPCPositiveThisMonth)-Double.parseDouble(vnPCNegativeThisMonth))+" < 70000 THEN 13500"
				+" 						 ELSE 19000 END AS FA ,"
				+ "                      CASE WHEN SUM(persistency.PRST_BIZRT) IS NULL OR SUM(persistency.SOLI_BIZRT) IS NULL THEN NULL ELSE NVL(CAST(((SUM(persistency.PRST_BIZRT) / SUM(persistency.SOLI_BIZRT)) * 100) AS Decimal(20,2)),0) END AS PERSISTENCY_RATE"
				+" 				FROM tsli_sfc_commission_positive  commission_positive"
				+ "              LEFT JOIN tsli_sfc_agent_structure  agent_structure"
				+ "               ON commission_positive.PE_NO_COL = agent_structure.PE_NO "
				+ "               LEFT JOIN tsli_standard_activity  tsli_standard_activity"
				+ "               ON tsli_standard_activity.PE_NO = agent_structure.PE_NO  "
				+ "               LEFT JOIN tsli_sfc_commission_negative  commission_negative "
				+ "               ON commission_negative.PE_NO_COL = commission_positive.PE_NO_COL AND commission_negative.clos_ym = ?"
				+ "               LEFT JOIN TSLI_PERSISTENCY persistency "
				+ "               ON persistency.PE_NO = agent_structure.PE_NO  "
				+ "                    WHERE 	( agent_structure.fst_enco_mms BETWEEN 1 AND 12 ) "
				+ "                      AND commission_positive.clos_ym = ?  "
				+ "						 AND agent_structure.dty_cd  in ('S10240205','S10240206') "
				+ "                    GROUP BY 	commission_positive.clos_ym,	agent_structure.PE_NO ,agent_structure.fst_enco_mms , tsli_standard_activity.bizrt_itm_data)) THIS_MONTH"
				+ "        LEFT JOIN "
				+ "        (SELECT clos_ym,"
				+ "                PE_NO,"
				+ "                fst_enco_mms,"
				+ "                PC,"
				+ "                FA,"
				+ "                CASE WHEN PERSISTENCY_RATE IS NULL THEN 'N/A' ELSE CAST(PERSISTENCY_RATE AS VARCHAR(3)) END AS PERSISTENCY_RATE,"
				+"CASE"
				+ "				 WHEN fst_enco_mms = '1'  THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '2'  THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '3' AND PERSISTENCY_RATE >= 98 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '4' AND PERSISTENCY_RATE >= 97 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '5' AND PERSISTENCY_RATE >= 96 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '6' AND PERSISTENCY_RATE >= 95 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '7' AND PERSISTENCY_RATE >= 94 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '8' AND PERSISTENCY_RATE >= 93 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '9' AND PERSISTENCY_RATE >= 92 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '10' AND PERSISTENCY_RATE >= 91 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '11' AND PERSISTENCY_RATE >= 90 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '12' AND PERSISTENCY_RATE >= 89 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 ELSE "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN (FA*0.90) ELSE 0 END"
				+ "				 END FA_TOTAL,"
				+ "				bizrt_itm_data"
				+ "        FROM(SELECT commission_positive.clos_ym,"
				+ "                      agent_structure.PE_NO,"
				+ "                      agent_structure.fst_enco_mms,"
				+ "                      tsli_standard_activity.bizrt_itm_data,"
				+" "+(Double.parseDouble(vnPCPositiveBeforeOneMonth)-Double.parseDouble(vnPCNegativeBeforeOneMonth))+" AS PC,"
				+" CASE "
				+" WHEN "+(Double.parseDouble(vnPCPositiveBeforeOneMonth)-Double.parseDouble(vnPCNegativeBeforeOneMonth))+" < 10000 THEN 0"
				+" WHEN "+(Double.parseDouble(vnPCPositiveBeforeOneMonth)-Double.parseDouble(vnPCNegativeBeforeOneMonth))+" < 20000 THEN 2500"
				+" WHEN "+(Double.parseDouble(vnPCPositiveBeforeOneMonth)-Double.parseDouble(vnPCNegativeBeforeOneMonth))+" < 30000 THEN 5000"
				+" WHEN "+(Double.parseDouble(vnPCPositiveBeforeOneMonth)-Double.parseDouble(vnPCNegativeBeforeOneMonth))+" < 40000 THEN 7500"
				+" WHEN "+(Double.parseDouble(vnPCPositiveBeforeOneMonth)-Double.parseDouble(vnPCNegativeBeforeOneMonth))+" < 50000 THEN 10500"
				+" WHEN "+(Double.parseDouble(vnPCPositiveBeforeOneMonth)-Double.parseDouble(vnPCNegativeBeforeOneMonth))+" < 70000 THEN 13500"
				+ " 						 ELSE 19000 END AS FA ,"
				+ "                      CASE WHEN SUM(persistency.PRST_BIZRT) IS NULL OR SUM(persistency.SOLI_BIZRT) IS NULL THEN NULL ELSE NVL(CAST(((SUM(persistency.PRST_BIZRT) / SUM(persistency.SOLI_BIZRT)) * 100) AS Decimal(20,2)),0) END AS PERSISTENCY_RATE"
				+" 				FROM tsli_sfc_commission_positive  commission_positive"
				+ "              LEFT JOIN tsli_sfc_agent_structure  agent_structure"
				+ "               ON commission_positive.PE_NO_COL = agent_structure.PE_NO "
				+ "               LEFT JOIN tsli_standard_activity  tsli_standard_activity"
				+ "               ON tsli_standard_activity.PE_NO = agent_structure.PE_NO  "
				+ "               LEFT JOIN tsli_sfc_commission_negative  commission_negative "
				+ "               ON commission_negative.PE_NO_COL = commission_positive.PE_NO_COL AND commission_negative.clos_ym = ?"
				+ "               LEFT JOIN TSLI_PERSISTENCY persistency "
				+ "               ON persistency.PE_NO = agent_structure.PE_NO  "
				+ "                    WHERE 	( agent_structure.fst_enco_mms BETWEEN 1 AND 12 ) "
				+ "                      AND commission_positive.clos_ym =TO_CHAR(ADD_MONTHS(TO_DATE( ? ,'YYYYMM'),-1),'YYYYMM')"
				+ "						 AND agent_structure.dty_cd  in ('S10240205','S10240206') "
				+ "                    GROUP BY 	commission_positive.clos_ym,	agent_structure.PE_NO ,agent_structure.fst_enco_mms , tsli_standard_activity.bizrt_itm_data)) BEFORE_ONE_MONTH"
				+ "         on THIS_MONTH.PE_NO = BEFORE_ONE_MONTH.PE_NO"
				+ "         LEFT JOIN"
				+ "        (SELECT clos_ym,"
				+ "                PE_NO,"
				+ "                fst_enco_mms,"
				+ "                PC,"
				+ "                FA,"
				+ "                CASE WHEN PERSISTENCY_RATE IS NULL THEN 'N/A' ELSE CAST(PERSISTENCY_RATE AS VARCHAR(3)) END AS PERSISTENCY_RATE,"
				+"CASE"
				+ "				 WHEN fst_enco_mms = '1'  THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '2'  THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '3' AND PERSISTENCY_RATE >= 98 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '4' AND PERSISTENCY_RATE >= 97 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '5' AND PERSISTENCY_RATE >= 96 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '6' AND PERSISTENCY_RATE >= 95 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '7' AND PERSISTENCY_RATE >= 94 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '8' AND PERSISTENCY_RATE >= 93 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '9' AND PERSISTENCY_RATE >= 92 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '10' AND PERSISTENCY_RATE >= 91 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '11' AND PERSISTENCY_RATE >= 90 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 WHEN fst_enco_mms = '12' AND PERSISTENCY_RATE >= 89 THEN "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN FA ELSE 0 END"
				+ "				 ELSE "
				+ "				 CASE WHEN bizrt_itm_data = 'Y' THEN (FA*0.90) ELSE 0 END"
				+ "				 END FA_TOTAL,"
				+ "				bizrt_itm_data"
				+ "        FROM(SELECT commission_positive.clos_ym,"
				+ "                      agent_structure.PE_NO,"
				+ "                      agent_structure.fst_enco_mms,"
				+ "                      tsli_standard_activity.bizrt_itm_data,"
				+" "+(Double.parseDouble(vnPCPositiveBeforeTwoMonth)-Double.parseDouble(vnPCNegativeBeforeTwoMonth))+" AS PC,"
				+" CASE "
				+" WHEN "+(Double.parseDouble(vnPCPositiveBeforeTwoMonth)-Double.parseDouble(vnPCNegativeBeforeTwoMonth))+" < 10000 THEN 0"
				+" WHEN "+(Double.parseDouble(vnPCPositiveBeforeTwoMonth)-Double.parseDouble(vnPCNegativeBeforeTwoMonth))+" < 20000 THEN 2500"
				+" WHEN "+(Double.parseDouble(vnPCPositiveBeforeTwoMonth)-Double.parseDouble(vnPCNegativeBeforeTwoMonth))+" < 30000 THEN 5000"
				+" WHEN "+(Double.parseDouble(vnPCPositiveBeforeTwoMonth)-Double.parseDouble(vnPCNegativeBeforeTwoMonth))+" < 40000 THEN 7500"
				+" WHEN "+(Double.parseDouble(vnPCPositiveBeforeTwoMonth)-Double.parseDouble(vnPCNegativeBeforeTwoMonth))+" < 50000 THEN 10500"
				+" WHEN "+(Double.parseDouble(vnPCPositiveBeforeTwoMonth)-Double.parseDouble(vnPCNegativeBeforeTwoMonth))+" < 70000 THEN 13500"
				+ " 						 ELSE 19000 END AS FA ,"
				+ "                      CASE WHEN SUM(persistency.PRST_BIZRT) IS NULL OR SUM(persistency.SOLI_BIZRT) IS NULL THEN NULL ELSE NVL(CAST(((SUM(persistency.PRST_BIZRT) / SUM(persistency.SOLI_BIZRT)) * 100) AS Decimal(20,2)),0) END AS PERSISTENCY_RATE"
				+" 				FROM tsli_sfc_commission_positive  commission_positive"
				+ "              LEFT JOIN tsli_sfc_agent_structure  agent_structure"
				+ "               ON commission_positive.PE_NO_COL = agent_structure.PE_NO "
				+ "               LEFT JOIN tsli_standard_activity  tsli_standard_activity"
				+ "               ON tsli_standard_activity.PE_NO = agent_structure.PE_NO  "
				+ "               LEFT JOIN tsli_sfc_commission_negative  commission_negative "
				+ "               ON commission_negative.PE_NO_COL = commission_positive.PE_NO_COL AND commission_negative.clos_ym = ?"
				+ "               LEFT JOIN TSLI_PERSISTENCY persistency "
				+ "               ON persistency.PE_NO = agent_structure.PE_NO  "
				+ "                    WHERE 	( agent_structure.fst_enco_mms BETWEEN 1 AND 12 ) "
				+ "                      AND commission_positive.clos_ym =TO_CHAR(ADD_MONTHS(TO_DATE( ? ,'YYYYMM'),-2),'YYYYMM') "
				+ "						 AND agent_structure.dty_cd  in ('S10240205','S10240206') "
				+ "                    GROUP BY 	commission_positive.clos_ym,	agent_structure.PE_NO ,agent_structure.fst_enco_mms , tsli_standard_activity.bizrt_itm_data)) BEFORE_TWO_MONTH"
				+ "        on THIS_MONTH.PE_NO = BEFORE_TWO_MONTH.PE_NO)"
				+ "	WHERE PE_NO = ?";

		List<Map<String, Object>> result = db.queryList(vaSql,paClosYm,paClosYm,paClosYm,paClosYm,paClosYm,paClosYm,paPeNo);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}
	
	public String findPaymentRate(String vaPeNoBm, String vaClosYm){
		String vaSql = "select count(*) AS GRADER_AMOUNT, "
								+" CASE "
									 +" WHEN count (*) < 12 THEN 0.07 "
									 +" WHEN count (*) < 24 THEN 0.12 "
									 +" ELSE 0.15 END AS RATE "
								 +" FROM (SELECT	p.pe_no_soli , SUM(NVL(p.AFYP,0)+NVL(p.ARYP2,0)) AS PC "
									 +" FROM tsli_sfc_commission_positive p "
				           +" WHERE p.pe_no_mm = ? AND p.clos_ym = ? "
                   +" GROUP BY p.clos_ym , p.pe_no_soli  HAVING SUM(p.AFYP+p.ARYP2) >= 10000) ";
		Map<String, Object> result = db.querySingle(vaSql,vaPeNoBm,vaClosYm);
		if (result != null) {
			return result.get("RATE").toString().trim()+"/"+ result.get("GRADER_AMOUNT").toString().trim();
		} else {
			return null;
		}
	}
	
	
	public String getPCPositive(String vaPeNo, String vaClosYm){
		String vaSql = "SELECT NVL(SUM(NVL(commission_positive.AFYP,0)+NVL(commission_positive.ARYP2,0)),0) AS PC"
				+ "         FROM tsli_sfc_commission_positive  commission_positive"
				+ "				              LEFT JOIN tsli_sfc_agent_structure  agent_structure"
				+ "				               ON commission_positive.PE_NO_COL = agent_structure.PE_NO "
				+ "				 WHERE  ( agent_structure.fst_enco_mms BETWEEN 1 AND 12 ) "
				+ "				 AND commission_positive.clos_ym = ?"
				+ "				 AND agent_structure.dty_cd  in ('S10240205','S10240206') "
				+ "         AND commission_positive.PE_NO_COL = ?";
		Map<String, Object> result = db.querySingle(vaSql,vaClosYm,vaPeNo);
		if (result != null) {
			return result.get("PC").toString().trim();
		} else {
			return null;
		}
	}
	
	public List<Map<String, Object>> getPCPositiveDetail(String vaPeNo, String vaClosYm) {

		 
		String vaSql = "SELECT commission_positive.CLOS_YM,commission_positive.PE_NO_COL,commission_positive.PO_NO,NVL(commission_positive.AFYP,0) AS AFYP , NVL(commission_positive.ARYP2,0) AS ARYP2,NVL(NVL(commission_positive.AFYP,0)+NVL(commission_positive.ARYP2,0),0) AS PC"
				+ "         FROM tsli_sfc_commission_positive  commission_positive"
				+ "				              LEFT JOIN tsli_sfc_agent_structure  agent_structure"
				+ "				               ON commission_positive.PE_NO_COL = agent_structure.PE_NO "
				+ "				 WHERE  ( agent_structure.fst_enco_mms BETWEEN 1 AND 12 ) "
				+ "				 AND commission_positive.clos_ym = ?"
				+ "				 AND agent_structure.dty_cd  in ('S10240205','S10240206') "
				+ "         AND commission_positive.PE_NO_COL = ?";

		List<Map<String, Object>> result = db.queryList(vaSql,vaClosYm,vaPeNo);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}
	
	public String getPCNegative(String vaPeNo, String vaClosYm){
		String vaSql = "SELECT NVL(SUM(NVL(commission_negative.AFYP,0)+NVL(commission_negative.ARYP2,0)),0) AS PC"
				+ "         FROM tsli_sfc_commission_negative  commission_negative"
				+ "				              LEFT JOIN tsli_sfc_agent_structure  agent_structure"
				+ "				               ON commission_negative.PE_NO_COL = agent_structure.PE_NO"
				+ "				 WHERE  ( agent_structure.fst_enco_mms BETWEEN 1 AND 12 ) "
				+ "				 AND commission_negative.clos_ym = ?"
				+ "				 AND agent_structure.dty_cd  in ('S10240205','S10240206') "
				+ "         AND commission_negative.PE_NO_COL = ?";

		Map<String, Object> result = db.querySingle(vaSql,vaClosYm,vaPeNo);
		if (result != null) {
			return result.get("PC").toString().trim();
		} else {
			return null;
		}
	}
	
	public List<Map<String, Object>> getPCNegativeDetail(String vaPeNo, String vaClosYm) {

		 
		String vaSql = "SELECT commission_negative.CLOS_YM,commission_negative.PE_NO_COL,commission_negative.PO_NO,NVL(commission_negative.AFYP,0) AS AFYP , NVL(commission_negative.ARYP2,0) AS ARYP2,NVL(NVL(commission_negative.AFYP,0)+NVL(commission_negative.ARYP2,0),0) AS PC"
				+ "         FROM tsli_sfc_commission_negative  commission_negative"
				+ "				              LEFT JOIN tsli_sfc_agent_structure  agent_structure"
				+ "				               ON commission_negative.PE_NO_COL = agent_structure.PE_NO"
				+ "				 WHERE  ( agent_structure.fst_enco_mms BETWEEN 1 AND 12 ) "
				+ "				 AND commission_negative.clos_ym = ?"
				+ "				 AND agent_structure.dty_cd  in ('S10240205','S10240206') "
				+ "         AND commission_negative.PE_NO_COL = ?";

		List<Map<String, Object>> result = db.queryList(vaSql,vaClosYm,vaPeNo);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	public String findTotalGrader(String vaClosYm, String vaPeNoMm) {
		String vaSql = "SELECT NVL(SUM(MONEY_PER_PERSON),0) AS TOTAL_GRADER FROM ( SELECT GRADER, SUM(BATH_PER_PERSON) AS MONEY_PER_PERSON "
								 +" FROM (SELECT p.pe_no_col ,SUM(p.AFYP+p.ARYP2) AS PC , CASE "
									 +" WHEN SUM(p.AFYP+p.ARYP2) < 10000  THEN '0' "
									 +" WHEN SUM(p.AFYP+p.ARYP2) < 20000  THEN 'C' "
									 +" WHEN SUM(p.AFYP+p.ARYP2) < 30000  THEN 'B' "
									 +" WHEN SUM(p.AFYP+p.ARYP2) < 40000  THEN 'A' "
									 +" WHEN SUM(p.AFYP+p.ARYP2) < 50000  THEN 'S' "
									 +" WHEN SUM(p.AFYP+p.ARYP2) < 70000  THEN 'SS' "
									 +" ELSE 'SSS' END  AS GRADER, "
								 +" CASE WHEN SUM(p.AFYP+p.ARYP2) < 10000  THEN 0 "
									 +" WHEN SUM(p.AFYP+p.ARYP2) < 20000  THEN 350 "
									 +" WHEN SUM(p.AFYP+p.ARYP2) < 30000  THEN 550 "
									 +" WHEN SUM(p.AFYP+p.ARYP2) < 40000  THEN 750 "
									 +" WHEN SUM(p.AFYP+p.ARYP2) < 50000  THEN 950 "
									 +" WHEN SUM(p.AFYP+p.ARYP2) < 70000  THEN 1150 "
                +" ELSE 1500 END  AS BATH_PER_PERSON "
								 +" FROM tsli_sfc_commission_positive p	"
				         +" WHERE p.clos_ym = ? AND p.PE_NO_MM = ?  GROUP BY p.pe_no_col "
				         +" HAVING SUM(p.AFYP+p.ARYP2) >= 10000 ) "
				         +" GROUP BY GRADER )";
		Map<String, Object> result = db.querySingle(vaSql, vaClosYm, vaPeNoMm);
		if (result != null) {
			return result.get("TOTAL_GRADER").toString();
		} else {
			return null;
		}
	}
	
	public List<Map<String, Object>> findGraderList(String vaClosYm, String vaPeNoMm) {
	String vaSql = " SELECT GRADER, SUM(BATH_PER_PERSON) AS MONEY_PER_PERSON ,count(GRADER) AS PERSON_PER_GRADER, SUM(BATH_PER_PERSON)/count(GRADER) AS BATH_PER_ONE "
			+" FROM (SELECT p.pe_no_col ,SUM(p.AFYP+p.ARYP2) AS PC ,"
						+" CASE "
							+" WHEN SUM(p.AFYP+p.ARYP2) < 10000  THEN '0' "
							+" WHEN SUM(p.AFYP+p.ARYP2) < 20000  THEN 'C' "
							+" WHEN SUM(p.AFYP+p.ARYP2) < 30000  THEN 'B' "
							+" WHEN SUM(p.AFYP+p.ARYP2) < 40000  THEN 'A' "
							+" WHEN SUM(p.AFYP+p.ARYP2) < 50000  THEN 'S' "
							+" WHEN SUM(p.AFYP+p.ARYP2) < 70000  THEN 'SS' "
							+" ELSE 'SSS' END  AS GRADER, "
						+" CASE WHEN SUM(p.AFYP+p.ARYP2) < 10000  THEN 0 "
							+" WHEN SUM(p.AFYP+p.ARYP2) < 20000  THEN 350  "
							+" WHEN SUM(p.AFYP+p.ARYP2) < 30000  THEN 550 "
							+" WHEN SUM(p.AFYP+p.ARYP2) < 40000  THEN 750 "
							+" WHEN SUM(p.AFYP+p.ARYP2) < 50000  THEN 950 "
							+" WHEN SUM(p.AFYP+p.ARYP2) < 70000  THEN 1150 "
							+" ELSE 1500 END  AS BATH_PER_PERSON "
					+" FROM tsli_sfc_commission_positive p "		 		       
					+" WHERE p.clos_ym = ? AND p.PE_NO_MM = ?  GROUP BY p.pe_no_col "
					+" HAVING SUM(p.AFYP+p.ARYP2) >= 10000 ) "
					+" GROUP BY GRADER "
					+" ORDER BY BATH_PER_ONE DESC";
		List<Map<String, Object>> result = db.queryList(vaSql, vaClosYm, vaPeNoMm);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}
	
	public String findPcOfMM(String vaPeNoMm, String vaClosYm){
	String vaSql = "select NVL(TO_CHAR(SUM(PC),'999,999,999.90'), 0) AS PC"
			+ " FROM(SELECT commission_positive.CLOS_YM, commission_positive.PE_NO_COL,commission_positive.PO_NO,commission_positive.AFYP, commission_positive.ARYP2,"
			+ " sum( commission_positive.AFYP+commission_positive.ARYP2) AS PC FROM tsli_sfc_commission_positive  commission_positive "
			+ " WHERE commission_positive.pe_no_mm = ? AND commission_positive.clos_ym = ?  "
			+ " GROUP BY commission_positive.PE_NO_MM, commission_positive.PE_NO_COL, commission_positive.PO_NO , commission_positive.CLOS_YM,"
			+ " commission_positive.AFYP, commission_positive.ARYP2) ";
		Map<String, Object> result = db.querySingle(vaSql,vaPeNoMm,vaClosYm);
		if (result != null) {
			return result.get("PC").toString().trim();
		} else {
			return null;
		}
	}

	public String findActiveProducer(String vaClosYm) {
		String vaSql = "SELECT count(*) AS ACTIVE_AMOUNT, "
								+" CASE  WHEN COUNT(*) < 1  THEN 0 "
									  +" WHEN COUNT(*) < 15 THEN 2000 "
									  +" WHEN COUNT(*) < 20 THEN 3000 "
									  +" WHEN COUNT(*) < 25 THEN 4000 "
									  +" WHEN COUNT(*) < 30 THEN 5000 "
									  +" ELSE 7000 END AS ACTIVE_PRODUCER "
									  +" FROM (SELECT p.pe_no_col ,SUM(p.FYP) AS   SUM_FYP "
									  		+" FROM TSLI_SFC_COMMISSION_POSITIVE p "
									  		+" WHERE p.cont_mms = 1 AND p.FYP >= 2000 AND p.clos_ym = ? GROUP BY p.pe_no_col)";
		Map<String, Object> result = db.querySingle(vaSql,vaClosYm);
		if (result != null) {
			return result.get("ACTIVE_PRODUCER").toString()+"/"+result.get("ACTIVE_AMOUNT");
		} else {
			return "0";
		}
	}
	 public String getSumPcForOE(String paClosYm, String paPeNoMM) {
			String vaSql = "SELECT commission_positive.PE_NO_MM,SUM(commission_positive.AFYP+commission_positive.ARYP2) AS PC "
							+" FROM tsli_sfc_commission_positive  commission_positive " 
							+" WHERE  commission_positive.PE_NO_MM = ? AND clos_ym = ? "
							+" GROUP BY commission_positive.PE_NO_MM ";

			Map<String, Object> result = db.querySingle(vaSql, paPeNoMM, paClosYm);
			if (result != null) {
				return (String) result.get("PC").toString();
			} else {
				return null;
			}
		}
	 
	 public String getSumARYPForOE(String paClosYm, String paPeNoMM) {
			String vaSql = "SELECT commission_positive.PE_NO_MM,SUM(commission_positive.ARYP) * 0.005 AS ARYP "
								+" FROM tsli_sfc_commission_positive  commission_positive "
								+" WHERE clos_ym = ? AND commission_positive.CONT_MMS between 13 and 60 AND commission_positive.PE_NO_MM = ? "
								+" GROUP BY commission_positive.PE_NO_MM ";

			Map<String, Object> result = db.querySingle(vaSql, paClosYm, paPeNoMM);
			if (result != null) {
				return (String) result.get("ARYP").toString();
			} else {
				return null;
			}
		}

	public List<Map<String, Object>> findClosYmOfFeeLook(String paPeNoMM, String paClosYm) {
		String vaSql = "SELECT DISTINCT(CLOS_YM),PO_NO "
				+ " FROM tsli_sfc_commission_positive "
				+ " WHERE PO_NO IN (SELECT PO_NO "
								+ " FROM tsli_sfc_commission_negative  commission_negative "
								+ " WHERE  commission_negative.PE_NO_MM = ? AND commission_negative.clos_ym = ?)";
		List<Map<String, Object>> result = db.queryList(vaSql, paPeNoMM,paClosYm);
		return result;
	}
}
